﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;


namespace DAL
{
    public class QuanLyUserQuery : DataProvide
    {


        public static List<QuanLyUserDAL> LayDanhSachUser()
        {
            SqlConnection chuoiKetNoi = null;
            List<QuanLyUserDAL> dsUser = new List<QuanLyUserDAL>();
            try
            {
                chuoiKetNoi = KetNoi();
                string sqlString = String.Format(@"SELECT NguoiDung.TaiKhoan, NguoiDung.MatKhau, NguoiDung.MaQuyen, NguoiDung.HoTen, NguoiDung.DiaChi, NguoiDung.Email, NguoiDung.SoDienThoai, NguoiDungCuoi.DiemTichLuy, NguoiDungCuoi.GhiChu, NguoiDung.GiaTriTaiKhoan FROM NguoiDung,NguoiDungCuoi WHERE NguoiDung.TaiKhoan=NguoiDungCuoi.TaiKhoan ");
               
                SqlCommand lenh = new SqlCommand(sqlString, chuoiKetNoi);

                SqlDataReader dr = lenh.ExecuteReader();
                while (dr.Read())
                {
                    QuanLyUserDAL us = new QuanLyUserDAL();
                    us.TaiKhoan = dr.GetString(0);
                    if (!dr.IsDBNull(1))
                        us.MatKhau = dr.GetString(1);
                    if (!dr.IsDBNull(2))
                        us.MaQuyen = dr.GetString(2);
                    if (!dr.IsDBNull(3))
                        us.HoTen = dr.GetString(3);
                    if (!dr.IsDBNull(4))
                        us.DiaChi = dr.GetString(4);
                    if (!dr.IsDBNull(5))
                        us.Email = dr.GetString(5);
                    if (!dr.IsDBNull(6))
                        us.SoDienThoai = dr.GetString(6); 
                    if (!dr.IsDBNull(7))
                        us.DiemtichLuy = dr.GetInt32(7);
                    if (!dr.IsDBNull(8))
                        us.GhiChu = dr.GetString(8);
                    if (!dr.IsDBNull(9))
                        us.GiaTriTK = float.Parse(dr.GetDouble(9).ToString());
                    dsUser.Add(us);
                }

            }
            finally
            {
                if (chuoiKetNoi != null && chuoiKetNoi.State == System.Data.ConnectionState.Open)
                    chuoiKetNoi.Close();
            }
            return dsUser;
        }


        public static bool ThemUserNguoiDung(string tk, string mk, string mq, string ht, string dc, string em, string sdt, float gttk)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "insert into NguoiDung values(N'"+ tk +"','" + mk + "','" + mq + "','" + ht + "','" + dc + "','" + em + "','" + sdt + "', '"+ gttk +"')";


                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();
            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static bool ThemUserNguoiDungCuoi(string tk, int dtl, string gc)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();


                string insertCommand = "insert into NguoiDungCuoi values(N'" + tk + "','" + dtl + "','" + gc + "')";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();


            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static List<QuanLyUserDAL> TimUser(string tk)
        {
            SqlConnection ketNoi = null;
            List<QuanLyUserDAL> dsUser = new List<QuanLyUserDAL>();
            try
            {
                ketNoi = KetNoi();

                string insertCommand = "SELECT NguoiDung.TaiKhoan, NguoiDung.MatKhau, NguoiDung.MaQuyen, NguoiDung.HoTen, NguoiDung.DiaChi, NguoiDung.Email, NguoiDung.SoDienThoai, NguoiDungCuoi.DiemTichLuy, NguoiDungCuoi.GhiChu, NguoiDung.GiaTriTaiKhoan FROM NguoiDung,NguoiDungCuoi WHERE NguoiDung.TaiKhoan like '%"+tk+"%' and  NguoiDungCuoi.TaiKhoan = NguoiDung.TaiKhoan ";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                SqlDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    QuanLyUserDAL ch = new QuanLyUserDAL();
                    ch.TaiKhoan = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        ch.MatKhau = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        ch.MaQuyen = boDoc.GetString(2);
                    if (!boDoc.IsDBNull(3))
                        ch.HoTen = boDoc.GetString(3);
                    if (!boDoc.IsDBNull(4))
                        ch.DiaChi = boDoc.GetString(4);
                    if (!boDoc.IsDBNull(5))
                        ch.Email = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        ch.SoDienThoai = boDoc.GetString(6);
                    if (!boDoc.IsDBNull(7))
                        ch.DiemtichLuy = boDoc.GetInt32(7);
                    if (!boDoc.IsDBNull(8))
                        ch.GhiChu = boDoc.GetString(8);
                    if (!boDoc.IsDBNull(9))
                        ch.GiaTriTK = float.Parse(boDoc.GetDouble(9).ToString());
                    dsUser.Add(ch);
                }
            }
            catch (Exception)
            {
                dsUser = new List<QuanLyUserDAL>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsUser;
        }

        



        public static bool XoaUser(string tk)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "delete from   NguoiDungCuoi  WHERE	TaiKhoan ='" + tk + "'";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();

                string insertCommand1 = "delete from   NguoiDung  WHERE	TaiKhoan ='" + tk + "'";

                SqlCommand lenh1 = new SqlCommand(insertCommand1, ketNoi);

                lenh1.ExecuteNonQuery();
            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }


        public static bool SuaUserNguoiDung(string tk, string mk, string mq, string ht, string dc, string em, string sdt, float gttk)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();
                string insertCommand = "UPDATE	NguoiDung SET	MatKhau = '" +
                    mk + "'," + " MaQuyen = '" +
                    mq + "'," + " HoTen = '" +
                    ht + "'," + " DiaChi = '" +
                    dc + "'," + " Email = '" +
                    em + "'," + " SoDienThoai = '" +
                    sdt + "', "+ " GiaTriTaiKhoan = '"+ 
                    gttk + "' WHERE	TaiKhoan ='" + tk + "'";

                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();

            }
            catch (Exception)
            {
                ketQua = false;

            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

        public static bool SuaUserNguoiDungCuoi(string tk, int dtl, string gc)
        {
            SqlConnection ketNoi = null;
            bool ketQua = true;
            try
            {
                ketNoi = KetNoi();


                string insertCommand = "UPDATE	NguoiDungCuoi SET	DiemTichLuy = '" +
                                   dtl + "'," + " GhiChu = '" +
                                   gc + "' WHERE	TaiKhoan ='" + tk + "'";
                SqlCommand lenh = new SqlCommand(insertCommand, ketNoi);

                lenh.ExecuteNonQuery();


            }
            catch (Exception)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }

    }
        
}

